package agenceVoyageVirtuel.server.base;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import agenceVoyageVirtuel.client.beans.AdresseData;
import agenceVoyageVirtuel.client.beans.CarteData;
import agenceVoyageVirtuel.client.beans.CommandeData;
import agenceVoyageVirtuel.client.beans.PersonneData;
import agenceVoyageVirtuel.server.DB_Conn;

public class DB_Commande extends DB_Conn {
	/**
	 * constructor - nothing to do
	 */
	public DB_Commande() {
		// nothing to do
	}
	
	private List<CommandeData> initCommandeDatas(int idClient) {
		String query = "SELECT idCommande, dateDepart, timeStamp, t_carte.idCarte, numCarte, dateCarte, cleCarte, t_adresse.idAdresse, numero, rue, ville, codePostal, t_personne.idPersonne, prenom, nom"
			+ " FROM t_commande JOIN t_adresse, t_personne, t_carte" 
			+ " WHERE idClient=" + idClient
			+ " AND t_commande.idAdresse=t_adresse.idAdresse"
			+ " AND t_adresse.idPersonne=t_personne.idPersonne"
			+ " AND t_commande.idCarte=t_carte.idCarte;";

		// prepare for rpc transport
		List<CommandeData> commandeDatas = new ArrayList<CommandeData>();
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				commandeDatas.add(new CommandeData());

				commandeDatas.get(i).setIdCommande(result.getInt(1));
				commandeDatas.get(i).setDateDepartTour(result.getDate(2).toString());
				commandeDatas.get(i).setDatePassageCommande(result.getTimestamp(3).toString());
				//Carte de paiement
				commandeDatas.get(i).setCarte(new CarteData());
				commandeDatas.get(i).getCarte().setIdCarte(result.getInt(4));
				commandeDatas.get(i).getCarte().setNumCarte(result.getString(5));
				commandeDatas.get(i).getCarte().setDateCarte(result.getString(6));
				commandeDatas.get(i).getCarte().setCleCarte(result.getString(7));
				//Adresse livraison
				commandeDatas.get(i).setAdresseCommande(new AdresseData());
				commandeDatas.get(i).getAdresseCommande().setIdAdresse(result.getInt(8));
				commandeDatas.get(i).getAdresseCommande().setNumero(result.getInt(9));
				commandeDatas.get(i).getAdresseCommande().setRue(result.getString(10));
				commandeDatas.get(i).getAdresseCommande().setVille(result.getString(11));
				commandeDatas.get(i).getAdresseCommande().setCodePostal(result.getInt(12));
				//Destinataire
				commandeDatas.get(i).getAdresseCommande().setPersonne(new PersonneData());
				commandeDatas.get(i).getAdresseCommande().getPersonne().setIdPersonne(result.getInt(13));
				commandeDatas.get(i).getAdresseCommande().getPersonne().setPrenom(result.getString(14));
				commandeDatas.get(i).getAdresseCommande().getPersonne().setNom(result.getString(15));

				i++;
			}

			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

		// return datas
		return commandeDatas;
	}
	
	public List<CommandeData> getHistoriqueCommandes(int idClient) {
		// prepare for rpc transport
		List<CommandeData> commandeDatas = initCommandeDatas(idClient);
		
		for(CommandeData c : commandeDatas) {
			c.setAvatars(new DB_Avatar().getAvatars(c.getIdCommande()));
			c.setOptions(new DB_Option().getOptions(c.getIdCommande()));
			c.setSouvenirs(new DB_Souvenir().getSouvenirs(c.getIdCommande()));
			c.setVoyages(new DB_Voyage().getVoyages(c.getIdCommande()));
		}
		
		return commandeDatas;
	}
	
	public void commander (CommandeData commandeEnCours) {
		String query = "INSERT INTO t_commande (idAdresse, idCarte, idClient, dateDepart)"
				+ " VALUES (" + commandeEnCours.getAdresseCommande().getIdAdresse()
				+ ", " + commandeEnCours.getCarte().getIdCarte()
				+ ", " + commandeEnCours.getClient().getIdClient()
				+ ", " + commandeEnCours.getDateDepartTour()
				+ ");";
	
		try {
			Connection connection = getConn();
			PreparedStatement  prepareStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			prepareStatement.executeUpdate();
            ResultSet keys = prepareStatement.getGeneratedKeys();
            
            keys.next(); //idCommande
            commandeEnCours.setIdCommande(keys.getInt(1));
			
            keys.close();
            prepareStatement.close();
			connection.close();
			
			new DB_Avatar().commanderAvatars(commandeEnCours);
			new DB_Option().commanderOptions(commandeEnCours);
			new DB_Souvenir().commanderSouvenirs(commandeEnCours);
			new DB_Voyage().commanderVoyages(commandeEnCours);
		} catch(Exception e) {
			System.err.println("Mysql PrepareStatement Error: " + query);
			e.printStackTrace();
		}
		
		return;				
	}
}
